package LawsonTransformJava.pgm;


//OracleEmployeeDAO implementation of the 
//EmployeeDAO interface. This class can contain all
//Oracle specific code and SQL statements. 
//The client is thus shielded from knowing 
//these implementation details.

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.io.*;
import java.lang.reflect.InvocationTargetException;

import LawsonTransformJava.xref.XrefDepartment;
import LawsonTransformJava.xref.XrefJobCode;
import LawsonTransformJava.xref.XrefLocatCode;
import LawsonTransformJava.xref.XrefPosition;
import LawsonTransformJava.xref.XrefProcessLevel;
import LawsonTransformJava.xref.XrefUnionCode;

import LawsonTransformJava.dao.DAOFactoryOracleDB;
import LawsonTransformJava.dao.DAOPAEmpPos;
import LawsonTransformJava.dao.DAObject;
import LawsonTransformJava.data.TblPAEmpPos;

public class PA513 extends DAObject implements 
 DAOPAEmpPos, Runnable {
	
	private XrefJobCode xJobCodes = null;
	private XrefPosition xPositions = null;
	private XrefDepartment xDepartments = null;
	private XrefProcessLevel xProcessLevels = null;
//	private XrefEmpStatus xEmpStatuses = null;
	private XrefLocatCode xLocatCodes = null;
	private XrefUnionCode xUnionCodes = null;
	
		
public PA513(File output) throws SQLException {
		// initialization 
		try {
			this.output = new FileWriter(this.outputFile = output);
			init(false);
		} catch (IOException e) {
			// Could no write properties file
			System.out.println("IOException: " + e);
		}
	}

public PA513(File output, boolean allowBlanks) throws SQLException {
	// initialization 
	try {
		this.output = new FileWriter(this.outputFile = output);
		init(allowBlanks);
	} catch (IOException e) {
		// Could no write properties file
		System.out.println("IOException: " + e);
	}
}

 private void init(boolean allowBlanks) throws IOException, SQLException {
		// Set the data and xref variables
		data = DAOFactoryOracleDB.getDAOOracleDB(true);
		xJobCodes = new XrefJobCode(allowBlanks);
		xPositions = new XrefPosition();
		xDepartments = new XrefDepartment(allowBlanks);
		xProcessLevels = new XrefProcessLevel();
//		xEmpStatuses = new XrefEmpStatus();
		xLocatCodes = new XrefLocatCode(allowBlanks);
		xUnionCodes = new XrefUnionCode(allowBlanks);

		sqlData = "SELECT * FROM " + data.getSchema() + "." + TABLE_NAME;
		stmtData = data.getDataStatement();	    
		countStmtData = data.getCountStatement();
		//Set the Date Format for output
		df = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
}

public TblPAEmpPos findPAEmpPos() {
		try {
			String schemax=data.getSchema();			
			TblPAEmpPos pep = new TblPAEmpPos();
			sqlData = "SELECT " +
					schemax + "." + TABLE_NAME  + ".EMPLOYEE, " +
					schemax + "." + TABLE_NAME  + ".POS_LEVEL, " + 
					schemax + "." + TABLE_NAME  + ".EFFECT_DATE, " +
	           		schemax + "." + TABLE_NAME  + ".END_DATE, " + 
	           		schemax + "." + TABLE_NAME  + ".POSITION, " +  
	           		schemax + "." + TABLE_NAME  + ".JOB_CODE, " +  
	           		schemax + "." + TABLE_NAME  + ".PROCESS_LEVEL, " +
	           		schemax + "." + TABLE_NAME  + ".DEPARTMENT, " +  
	           		schemax + "." + TABLE_NAME  + ".USER_LEVEL, " +  
	           		schemax + "." + TABLE_NAME  + ".SUPERVISOR, " +  
	           		schemax + "." + TABLE_NAME  + ".SUPERVISOR_IND, " +
	           		schemax + "." + TABLE_NAME  + ".LOCAT_CODE, " +  
	           		schemax + "." + TABLE_NAME  + ".UNION_CODE, " +  
	           		schemax + "." + TABLE_NAME  + ".FTE, " + 
	           		schemax + "." + TABLE_NAME  + ".PAY_RATE, " + 
	           		schemax + "." + TABLE_NAME  + ".SCHEDULE, " +
	           		schemax + "." + TABLE_NAME  + ".PAY_GRADE, " + 
	           		schemax + "." + TABLE_NAME  + ".PAY_STEP, " + 
	           		schemax + "." + TABLE_NAME  + ".WORK_SCHED, " + 
	           		schemax + "." + TABLE_NAME  + ".R_SHIFT, " + 
		           	schemax + "." + TABLE_NAME  + ".PRO_RATE_A_SAL, " +
		          	schemax + "." + TABLE_NAME  + ".ANNUAL_HOURS, " + 
		          	schemax + "." + TABLE_NAME  + ".EXP_COMPANY, " + 
		          	schemax + "." + TABLE_NAME  + ".EXP_ACCT_UNIT, " + 
		          	schemax + "." + TABLE_NAME  + ".EXP_ACCOUNT, " + 
		          	schemax + "." + TABLE_NAME  + ".EXP_SUB_ACCT, " + 
		          	schemax + "." + TABLE_NAME  + ".ACTIVITY, " + 
		          	schemax + "." + TABLE_NAME  + ".ACCT_CATEGORY, " + 
		          	schemax + "." + TABLE_NAME  + ".DATE_STAMP, " + 
		          	schemax + "." + TABLE_NAME  + ".TIME_STAMP, " +
		          	schemax + "." + TABLE_NAME  + ".USER_ID, " + 
		          	schemax + "." + TABLE_NAME  + ".CURRENCY_CODE, " + 
		          	schemax + "." + TABLE_NAME  + ".BASE_CURRENCY, " + 
		          	schemax + "." + TABLE_NAME  + ".BASE_PAY_RATE, " +
		          	schemax + "." + TABLE_NAME  + ".BARGAIN_UNIT, " + 
		          	schemax + "." + TABLE_NAME  + ".DATE_ASSIGN, " + 
		          	schemax + "." + TABLE_NAME  + ".USER_AMOUNT, " + 
		          	schemax + "." + TABLE_NAME2 + ".SALARY_CLASS " +
				"FROM "        + schemax + "." + TABLE_NAME  +
				" INNER JOIN " + schemax + "." + TABLE_NAME2 +
				" ON "         + schemax + "." + TABLE_NAME  + ".POSITION" +
				"="	       + schemax + "." + TABLE_NAME2 + ".POSITION";
               
			rslt = data.getDataStatement().executeQuery(sqlData);
			rsmd = rslt.getMetaData();
			if (rslt.next())
				hasMoreRecs = true;
			else
				hasMoreRecs = false;
			pep = (TblPAEmpPos) createObject(rslt, pep);
//			heu.setHRUserFlds((new HR515HRUserFlds()).findHRUserFlds(heu.getField_key()));	  
			return pep;
		} catch (SQLException e) {
		    // Could not connect to the database
			System.out.println("SQLException: " + e);
		} catch (InvocationTargetException ite) {
			System.out.println("InvocationException: " + ite);
		} catch (IllegalAccessException iae) {
			System.out.println("IllegalAccessException: " + iae);
		}
	  return null;
}

public TblPAEmpPos findPAEmpPos(String employeeNumber) {
	try {	
		String schemax=data.getSchema();		
		TblPAEmpPos pep = new TblPAEmpPos();
//		sqlData = "SELECT * FROM " + data.getSchema() + "." + TABLE_NAME + " WHERE " + TABLE_NAME + "='" + employeeNumber + "'";

		sqlData = "SELECT " +
				schemax + "." + TABLE_NAME  + ".EMPLOYEE, " +
				schemax + "." + TABLE_NAME  + ".POS_LEVEL, " + 
				schemax + "." + TABLE_NAME  + ".EFFECT_DATE, " +
           		schemax + "." + TABLE_NAME  + ".END_DATE, " + 
           		schemax + "." + TABLE_NAME  + ".POSITION, " +  
           		schemax + "." + TABLE_NAME  + ".JOB_CODE, " +  
           		schemax + "." + TABLE_NAME  + ".PROCESS_LEVEL, " +
           		schemax + "." + TABLE_NAME  + ".DEPARTMENT, " +  
           		schemax + "." + TABLE_NAME  + ".USER_LEVEL, " +  
           		schemax + "." + TABLE_NAME  + ".SUPERVISOR, " +  
           		schemax + "." + TABLE_NAME  + ".SUPERVISOR_IND, " +
           		schemax + "." + TABLE_NAME  + ".LOCAT_CODE, " +  
           		schemax + "." + TABLE_NAME  + ".UNION_CODE, " +  
           		schemax + "." + TABLE_NAME  + ".FTE, " + 
           		schemax + "." + TABLE_NAME  + ".PAY_RATE, " + 
           		schemax + "." + TABLE_NAME  + ".SCHEDULE, " +
           		schemax + "." + TABLE_NAME  + ".PAY_GRADE, " + 
           		schemax + "." + TABLE_NAME  + ".PAY_STEP, " + 
           		schemax + "." + TABLE_NAME  + ".WORK_SCHED, " + 
           		schemax + "." + TABLE_NAME  + ".R_SHIFT, " + 
	            schemax + "." + TABLE_NAME  + ".PRO_RATE_A_SAL, " +
	          	schemax + "." + TABLE_NAME  + ".ANNUAL_HOURS, " + 
	          	schemax + "." + TABLE_NAME  + ".EXP_COMPANY, " + 
	          	schemax + "." + TABLE_NAME  + ".EXP_ACCT_UNIT, " + 
	          	schemax + "." + TABLE_NAME  + ".EXP_ACCOUNT, " + 
	          	schemax + "." + TABLE_NAME  + ".EXP_SUB_ACCT, " + 
	          	schemax + "." + TABLE_NAME  + ".ACTIVITY, " + 
	          	schemax + "." + TABLE_NAME  + ".ACCT_CATEGORY, " + 
	          	schemax + "." + TABLE_NAME  + ".DATE_STAMP, " + 
	          	schemax + "." + TABLE_NAME  + ".TIME_STAMP, " +
	          	schemax + "." + TABLE_NAME  + ".USER_ID, " + 
	          	schemax + "." + TABLE_NAME  + ".CURRENCY_CODE, " + 
	          	schemax + "." + TABLE_NAME  + ".BASE_CURRENCY, " + 
	          	schemax + "." + TABLE_NAME  + ".BASE_PAY_RATE, " +
	          	schemax + "." + TABLE_NAME  + ".BARGAIN_UNIT, " + 
	          	schemax + "." + TABLE_NAME  + ".DATE_ASSIGN, " + 
	          	schemax + "." + TABLE_NAME  + ".USER_AMOUNT, " + 
	          	schemax + "." + TABLE_NAME2 + ".SALARY_CLASS " +
			"FROM "        + schemax + "." + TABLE_NAME  +
			" INNER JOIN " + schemax + "." + TABLE_NAME2 +
			" ON "         + schemax + "." + TABLE_NAME  + ".POSITION" +
			"="	       + schemax + "." + TABLE_NAME2 + ".POSITION";

		rslt = data.getDataStatement().executeQuery(sqlData);
		rsmd = rslt.getMetaData();
		rslt.next();
		//@ TODO Add the creation of an Employee object here
		pep = (TblPAEmpPos) createObject(rslt, pep);
		String temp_tc = pep.getPosition();
//		pep.setPAPosition((new PA513PAPosition()).findPosition(pep.getPosition()));
		return pep;
		//createFields(pep);
	} catch (SQLException e) {
	    // Could not connect to the database
		System.out.println("SQLException: " + e);
	} catch (InvocationTargetException ite) {
		System.out.println("InvocationException: " + ite);
	} catch (IllegalAccessException iae) {
		System.out.println("IllegalAccessException: " + iae);
	}
	return new TblPAEmpPos();
}

public TblPAEmpPos nextPAEmpPos() {

	  TblPAEmpPos pep = new TblPAEmpPos();
		try {
			if (rslt.next()) {
				hasMoreRecs = true;
				pep = (TblPAEmpPos) createObject(rslt, pep);
			} else
				hasMoreRecs = false;
			return pep;
		} catch (SQLException e) {
		    // Could not connect to the database
			System.out.println("SQLException: " + e);
		} catch (InvocationTargetException ite) {
			System.out.println("InvocationException: " + ite);
		} catch (IllegalAccessException iae) {
			System.out.println("IllegalAccessException: " + iae);
		}
	  return null;
}

public boolean hasMore () {
	  return hasMoreRecs;
	}

public int countPAEmpPos() {
	try {
		countRecs = data.getCountStatement().executeQuery("SELECT COUNT(*) FROM " + data.getSchema() + "." + TABLE_NAME + (whereClause==null?"":whereClause));
		countRecs.next();
		return recCount=countRecs.getInt(1);
		} catch (SQLException e) {
		    // Could not connect to the database
			System.out.println("SQLException: " + e);
		}
		return 0;
}

public void setWhereClause(String where) {
	  this.whereClause = where;
}

public void run() {
	  TblPAEmpPos pep = null;
	  
	  try {
		//Update current date/time
		Date now = new Date();
		System.out.println(outputFile.getName() + " Start " + df.format(now));
		//Write timestamp to output file
		output.write(outputFile.getName() + " Start " + df.format(now) + "\n");
		
		pep = findPAEmpPos();
		output.write(writePA513Layout(pep) + "\n");
		System.out.println(whereClause + " " + countPAEmpPos());
		while (hasMore()) {
			pep = nextPAEmpPos();
			output.write(writePA513Layout(pep) + "\n");
		}
		//Update current date/time
		now = new Date();
		System.out.println(outputFile.getName() + " Finish " + df.format(now));
		//Write timestamp to output file
		output.write(outputFile.getName() + " Finish " + df.format(now) + "\n");
		output.flush();
		output.close();
	} catch (IOException e) {
		// Could not write properties file
		// Could not create output file
		System.out.println("IOException: " + e);
	}

}

	//Output the PA513 format
	public String writePA513Layout(TblPAEmpPos pep) {
		return (
				"\""+"A"+"\"" + "," +	
				"1" + "," +
				pep.getEmployee() + "," +
				pep.getPos_level() + "," +
				pep.getEffect_date() + "," +
				pep.getEnd_date() + "," +
				xPositions.xlate(pep.getPosition()) + "," +
				xJobCodes.xlate(pep.getJob_code()) + "," +
				xProcessLevels.xlate(pep.getProcess_level()) + "," +				
				xDepartments.xlate(pep.getDepartment()) + "," +
				pep.getUser_level() + "," +
				pep.getSupervisor() + "," +
				pep.getSupervisor_ind() + "," +
				xLocatCodes.xlate(pep.getLocat_code()) + "," +
				xUnionCodes.xlate(pep.getUnion_code()) + "," +
				pep.getFte() + "," +
				pep.getPay_rate() + "," +
				pep.getSchedule() + "," +
				pep.getPay_grade() + "," +
				pep.getPay_step() + "," +
				pep.getWork_sched() + "," +
				pep.getR_shift() + "," +
				pep.getPro_rate_a_sal() + "," +
				pep.getAnnual_hours() + "," +
				pep.getExp_company() + "," +
				pep.getExp_acct_unit() + "," +
				pep.getExp_account() + "," +
				pep.getExp_sub_acct() + "," +
				pep.getActivity() + "," +
				pep.getAcct_category() + "," +
				pep.getDate_stamp() + "," +
				pep.getTime_stamp() + "," +
				pep.getUser_id() + "," +
				pep.getCurrency_code() + "," +
				pep.getBase_currency() + "," +
				pep.getBase_pay_rate() + "," +
				pep.getSalary_class()  + "," +
				pep.getBargain_unit() + "," +
				pep.getDate_assign() + "," +
				pep.getUser_amount()

				);
	}
}